using Dapper;
using Lzfy_Refund_Service.Models;
using Lzfy_Refund_Service.Services;
using System.Data;
using Microsoft.Data.SqlClient;
using TaskStatus = Lzfy_Refund_Service.Models.PasswordTaskStatus;

namespace Lzfy_Refund_Service.Repositories
{
    /// <summary>
    /// 密码更新任务数据访问实现
    /// </summary>
    public class PasswordUpdateTaskRepository : IPasswordUpdateTaskRepository
    {
        private readonly IDbConnectionFactory _connectionFactory;
        private readonly ILogger<PasswordUpdateTaskRepository> _logger;

        public PasswordUpdateTaskRepository(IDbConnectionFactory connectionFactory, ILogger<PasswordUpdateTaskRepository> logger)
        {
            _connectionFactory = connectionFactory;
            _logger = logger;
        }

        /// <summary>
        /// 创建密码更新任务
        /// </summary>
        public async Task<int> CreateTaskAsync(PasswordUpdateTask task)
        {
            const string sql = @"
                INSERT INTO lzfy_password_update_tasks 
                (task_id, patient_id, verify_code, verify_info_id, refund_password, modifier, modifier_card_type, modifier_card_code, 
                 scheduled_time, created_time, status, retry_count, max_retry_count)
                VALUES 
                (@TaskId, @PatientId, @VerifyCode, @VerifyInfoId, @RefundPassword, @Modifier, @ModifierCardType, @ModifierCardCode, 
                 @ScheduledTime, @CreatedTime, @Status, @RetryCount, @MaxRetryCount);
                SELECT CAST(SCOPE_IDENTITY() as int);";

            try
            {
                using var connection = _connectionFactory.CreateConnection();
                var id = await connection.QuerySingleAsync<int>(sql, task);
                _logger.LogInformation("创建密码更新任务成功，任务ID: {TaskId}, 患者ID: {PatientId}", task.TaskId, task.PatientId);
                return id;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "创建密码更新任务失败，任务ID: {TaskId}, 患者ID: {PatientId}", task.TaskId, task.PatientId);
                throw;
            }
        }

        /// <summary>
        /// 根据任务ID获取任务信息
        /// </summary>
        public async Task<PasswordUpdateTask?> GetTaskByIdAsync(string taskId)
        {
            const string sql = @"
                SELECT id, task_id as TaskId, patient_id as PatientId, verify_code as VerifyCode, 
                       verify_info_id as VerifyInfoId, refund_password as RefundPassword, modifier as Modifier, 
                       modifier_card_type as ModifierCardType, modifier_card_code as ModifierCardCode,
                       scheduled_time as ScheduledTime, created_time as CreatedTime, status as Status,
                       executed_time as ExecutedTime, error_message as ErrorMessage, 
                       retry_count as RetryCount, max_retry_count as MaxRetryCount
                FROM lzfy_password_update_tasks 
                WHERE task_id = @TaskId";

            try
            {
                using var connection = _connectionFactory.CreateConnection();
                return await connection.QuerySingleOrDefaultAsync<PasswordUpdateTask>(sql, new { TaskId = taskId });
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "获取任务信息失败，任务ID: {TaskId}", taskId);
                throw;
            }
        }

        /// <summary>
        /// 根据患者ID获取待执行的任务
        /// </summary>
        public async Task<List<PasswordUpdateTask>> GetPendingTasksByPatientIdAsync(string patientId)
        {
            const string sql = @"
                SELECT id, task_id as TaskId, patient_id as PatientId, verify_code as VerifyCode, 
                       verify_info_id as VerifyInfoId, refund_password as RefundPassword, modifier as Modifier, 
                       modifier_card_type as ModifierCardType, modifier_card_code as ModifierCardCode,
                       scheduled_time as ScheduledTime, created_time as CreatedTime, status as Status,
                       executed_time as ExecutedTime, error_message as ErrorMessage, 
                       retry_count as RetryCount, max_retry_count as MaxRetryCount
                FROM lzfy_password_update_tasks 
                WHERE patient_id = @PatientId AND status = @Status
                ORDER BY created_time DESC";

            try
            {
                using var connection = _connectionFactory.CreateConnection();
                var result = await connection.QueryAsync<PasswordUpdateTask>(sql, new { PatientId = patientId, Status = TaskStatus.Pending });
                return result.ToList();
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "获取患者待执行任务失败，患者ID: {PatientId}", patientId);
                throw;
            }
        }

        /// <summary>
        /// 获取需要执行的任务列表
        /// </summary>
        public async Task<List<PasswordUpdateTask>> GetTasksToExecuteAsync(DateTime currentTime, int limit = 100)
        {
            const string sql = @"
                SELECT TOP (@Limit) 
                       id, task_id as TaskId, patient_id as PatientId, verify_code as VerifyCode, 
                       verify_info_id as VerifyInfoId, refund_password as RefundPassword, modifier as Modifier, 
                       modifier_card_type as ModifierCardType, modifier_card_code as ModifierCardCode,
                       scheduled_time as ScheduledTime, created_time as CreatedTime, status as Status,
                       executed_time as ExecutedTime, error_message as ErrorMessage, 
                       retry_count as RetryCount, max_retry_count as MaxRetryCount
                FROM lzfy_password_update_tasks 
                WHERE status = @Status AND scheduled_time <= @CurrentTime
                ORDER BY scheduled_time ASC";

            try
            {
                using var connection = _connectionFactory.CreateConnection();
                var result = await connection.QueryAsync<PasswordUpdateTask>(sql, new { 
                    Status = TaskStatus.Pending, 
                    CurrentTime = currentTime, 
                    Limit = limit 
                });
                return result.ToList();
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "获取待执行任务列表失败");
                throw;
            }
        }

        /// <summary>
        /// 更新任务状态
        /// </summary>
        public async Task<bool> UpdateTaskStatusAsync(string taskId, string status, DateTime? executedTime = null, string? errorMessage = null)
        {
            const string sql = @"
                UPDATE lzfy_password_update_tasks 
                SET status = @Status, 
                    executed_time = @ExecutedTime, 
                    error_message = @ErrorMessage
                WHERE task_id = @TaskId";

            try
            {
                using var connection = _connectionFactory.CreateConnection();
                var rowsAffected = await connection.ExecuteAsync(sql, new { 
                    TaskId = taskId, 
                    Status = status, 
                    ExecutedTime = executedTime, 
                    ErrorMessage = errorMessage 
                });
                
                var success = rowsAffected > 0;
                if (success)
                {
                    _logger.LogInformation("更新任务状态成功，任务ID: {TaskId}, 新状态: {Status}", taskId, status);
                }
                else
                {
                    _logger.LogWarning("更新任务状态失败，任务ID: {TaskId}, 新状态: {Status}", taskId, status);
                }
                
                return success;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "更新任务状态异常，任务ID: {TaskId}, 新状态: {Status}", taskId, status);
                throw;
            }
        }

        /// <summary>
        /// 增加任务重试次数
        /// </summary>
        public async Task<bool> IncrementRetryCountAsync(string taskId, string? errorMessage = null)
        {
            const string sql = @"
                UPDATE lzfy_password_update_tasks 
                SET retry_count = retry_count + 1, 
                    error_message = @ErrorMessage,
                    status = CASE 
                        WHEN retry_count + 1 >= max_retry_count THEN @FailedStatus 
                        ELSE @PendingStatus 
                    END
                WHERE task_id = @TaskId";

            try
            {
                using var connection = _connectionFactory.CreateConnection();
                var rowsAffected = await connection.ExecuteAsync(sql, new { 
                    TaskId = taskId, 
                    ErrorMessage = errorMessage,
                    FailedStatus = TaskStatus.Failed,
                    PendingStatus = TaskStatus.Pending
                });
                
                var success = rowsAffected > 0;
                if (success)
                {
                    _logger.LogInformation("增加任务重试次数成功，任务ID: {TaskId}", taskId);
                }
                else
                {
                    _logger.LogWarning("增加任务重试次数失败，任务ID: {TaskId}", taskId);
                }
                
                return success;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "增加任务重试次数异常，任务ID: {TaskId}", taskId);
                throw;
            }
        }

        /// <summary>
        /// 取消任务
        /// </summary>
        public async Task<bool> CancelTaskAsync(string taskId)
        {
            const string sql = @"
                UPDATE lzfy_password_update_tasks 
                SET status = @Status 
                WHERE task_id = @TaskId AND status = @PendingStatus";

            try
            {
                using var connection = _connectionFactory.CreateConnection();
                var rowsAffected = await connection.ExecuteAsync(sql, new { 
                    TaskId = taskId, 
                    Status = TaskStatus.Cancelled,
                    PendingStatus = TaskStatus.Pending
                });
                
                var success = rowsAffected > 0;
                if (success)
                {
                    _logger.LogInformation("取消任务成功，任务ID: {TaskId}", taskId);
                }
                else
                {
                    _logger.LogWarning("取消任务失败，任务ID: {TaskId}", taskId);
                }
                
                return success;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "取消任务异常，任务ID: {TaskId}", taskId);
                throw;
            }
        }

        /// <summary>
        /// 取消患者的所有待执行任务
        /// </summary>
        public async Task<int> CancelPendingTasksByPatientIdAsync(string patientId)
        {
            const string sql = @"
                UPDATE lzfy_password_update_tasks 
                SET status = @Status 
                WHERE patient_id = @PatientId AND status = @PendingStatus";

            try
            {
                using var connection = _connectionFactory.CreateConnection();
                var rowsAffected = await connection.ExecuteAsync(sql, new { 
                    PatientId = patientId, 
                    Status = TaskStatus.Cancelled,
                    PendingStatus = TaskStatus.Pending
                });
                
                _logger.LogInformation("取消患者待执行任务成功，患者ID: {PatientId}, 取消数量: {Count}", patientId, rowsAffected);
                return rowsAffected;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "取消患者待执行任务异常，患者ID: {PatientId}", patientId);
                throw;
            }
        }

        /// <summary>
        /// 清理过期的已完成任务
        /// </summary>
        public async Task<int> CleanupCompletedTasksAsync(DateTime beforeDate)
        {
            const string sql = @"
                DELETE FROM lzfy_password_update_tasks 
                WHERE status IN (@CompletedStatus, @FailedStatus, @CancelledStatus) 
                AND created_time < @BeforeDate";

            try
            {
                using var connection = _connectionFactory.CreateConnection();
                var rowsAffected = await connection.ExecuteAsync(sql, new { 
                    CompletedStatus = TaskStatus.Completed,
                    FailedStatus = TaskStatus.Failed,
                    CancelledStatus = TaskStatus.Cancelled,
                    BeforeDate = beforeDate
                });
                
                _logger.LogInformation("清理过期任务成功，清理数量: {Count}", rowsAffected);
                return rowsAffected;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "清理过期任务异常");
                throw;
            }
        }

        /// <summary>
        /// 获取任务统计信息
        /// </summary>
        public async Task<Dictionary<string, int>> GetTaskStatisticsAsync()
        {
            const string sql = @"
                SELECT status, COUNT(*) as count
                FROM lzfy_password_update_tasks 
                GROUP BY status";

            try
            {
                using var connection = _connectionFactory.CreateConnection();
                var result = await connection.QueryAsync<dynamic>(sql);
                
                var statistics = new Dictionary<string, int>();
                foreach (var item in result)
                {
                    statistics[item.status] = item.count;
                }
                
                return statistics;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "获取任务统计信息异常");
                throw;
            }
        }
    }
}